package com.dp.service.impl3;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.SnAreaVo;
import com.dp.util.DataMap;
import com.dp.vo.NewsClientinfo;
import com.dp.vo.NewsGroup;
import com.dp.vo.NewsGroupSn;
import com.dp.vo.NewsGroupSnPK;
import com.dp.vo.NewsGroupuserset;
import com.dp.vo.NewsPlatforminfo;
import com.dp.vo.NewsSnArea;
import com.dp.vo.TTelDevice;

@Service
@Transactional
public class GroupServiceImpl3 extends BaseImpl {
	
	/**
	 * Find all the NewsGroup Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsGroup>
	 */
	public List<NewsGroup> findAllNewsGroupEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String userids=(String)hashMap.get("userids");
		List<NewsGroup> queryList=new ArrayList<NewsGroup>();
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsGroup n WHERE 1=1 ");
		if(group!=null){
			if(group.getGrouprulename()!=null&& group.getGrouprulename().toString().trim().length() > 0){
				sqlS.append(" AND n.grouprulename like '%"
						+ group.getGrouprulename().toString().trim() + "%'");
			}
			if(group.getMlocale()!=null&& group.getMlocale().toString().trim().length() > 0){
				sqlS.append(" AND n.mlocale like '%"
						+ group.getMlocale().toString().trim() + "%'");
			}
			if(group.getGrouptype()!=null&& group.getGrouptype().toString().trim().length() > 0){
				sqlS.append(" AND n.grouptype='"
						+ group.getGrouptype().toString().trim() + "'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.marea.areaid in ("
					+ areaids + ")");
		}
		if(userids!=null){
			sqlS.append(" AND n.userid in ("+userids+")");
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * 用户组信息总记录数
	 * @param hashMap
	 * @return
	 */
	public int findAllNewsGroupEntityCount(DataMap hashMap){
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String userids=(String)hashMap.get("userids");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.grouprulename) FROM NewsGroup n WHERE 1=1 ");
		if(group!=null){
			if(group.getGrouprulename()!=null&& group.getGrouprulename().toString().trim().length() > 0){
				sqlS.append(" AND n.grouprulename like '%"
						+ group.getGrouprulename().toString().trim() + "%'");
			}
			if(group.getMlocale()!=null&& group.getMlocale().toString().trim().length() > 0){
				sqlS.append(" AND n.mlocale like '%"
						+ group.getMlocale().toString().trim() + "%'");
			}
			if(group.getGrouptype()!=null&& group.getGrouptype().toString().trim().length() > 0){
				sqlS.append(" AND n.grouptype='"
						+ group.getGrouptype().toString().trim() + "'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.marea.areaid in ("
					+ areaids + ")");
		}
		if(userids!=null){
			sqlS.append(" AND n.userid in ("+userids+")");
		}
		try {
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * findNewsGroupById
	 * @param groupruleid
	 * @return NewsGroup
	 */
	public NewsGroup findNewsGroupById(Integer groupruleid) {
		NewsGroup o = null;
		try {
			o = this.em.find(NewsGroup.class, groupruleid);
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return o;
	}

	/**
	 * 已选客户端平台
	 * getSelectedplatforminfoListbyid
	 * @param groupruleid
	 * @return List<NewsPlatforminfo>
	 */
	public List<NewsPlatforminfo> getSelectedplatforminfoListbyid(Integer groupruleid) {
		List<NewsPlatforminfo> queryList=new ArrayList<NewsPlatforminfo>();
		try {
		
			this.sql="SELECT platformid,mplatform,magent,mserialnum,note FROM news_platforminfo WHERE platformid in" +
					"(SELECT platformid FROM news_grouprule WHERE groupruleid="+groupruleid+")";
		
			NewsPlatforminfo platforminfo=null;
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				platforminfo=new NewsPlatforminfo();
				platforminfo.setPlatformid(this.getString(o[0]));
				platforminfo.setMplatform(this.getString(o[1]));
				platforminfo.setMagent(this.getString(o[2]));
				platforminfo.setMserialnum(this.getString(o[3]));
				platforminfo.setNote(this.getString(o[4]));
				queryList.add(platforminfo);
			}
		} catch (Exception e) {
			logger.info("getSelectedplatforminfoListbyid fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/***
	 * 新增规则组信息
	 * addNewsGroup_Rule 
	 * @param DataMap
	 * @return Boolean
	 */
	public Boolean addNewsGroup_Rule(DataMap hashMap) {
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String platformids= (String)hashMap.get("platformids");
		String userId= (String)hashMap.get("userId");
		Boolean resultb=true;
		int groupruleid = 0;
		try {
//			this.init();
			
			//检查规则组是否存在
			this.sql="SELECT groupruleid FROM news_group WHERE grouprulename=? and mlocale=? and marea=? and note=? and grouptype=?";
		Query query=	this.em.createNativeQuery(sql);	
		query.setParameter(1, group.getGrouprulename());
		query.setParameter(2, group.getMlocale());
		query.setParameter(3, group.getMarea().getAreaid()+"");
		query.setParameter(4, group.getNote());
		query.setParameter(5, 1);
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				String charIdList = "";
				for (String str : platformids.split(",")) {
					charIdList = charIdList + "'" + str + "',";
				}
				if (!charIdList.equals("")) {
					charIdList = charIdList.substring(0, charIdList.length() - 1);
				} 
				this.sql="SELECT count(*) FROM news_grouprule WHERE groupruleid="+this.getString(o[0])+" and platformid in ("+charIdList+")";
//				this.prep=this.conn.prepareStatement(sql);	
//				this.prep.setInt(1, rs1.getInt(1));
//				this.rs2=prep.executeQuery();
				if(this.getOneInt()>0){
					return null;
				}
			}
			
			//增加规则组信息
			//this.sql="insert into news_group (grouprulename,mlocale,marea,note,grouptype,userid,createtime) values (?,?,?,?,1,'"+userId+"',now())";
			
			//NewsGroup ng=new NewsGroup();
			group.setUserid(userId);
			group.setGrouptype(1);
			group.setCreatetime(new Date());
			this.em.persist(group);
			groupruleid=group.getGroupruleid();
//			this.prep=this.conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
//			prep.setString(1, group.getGrouprulename());
//			prep.setString(2, group.getMlocale());
//			prep.setString(3,group.getMarea().getAreaid()+"");
//			prep.setString(4, group.getNote());
//			prep.executeUpdate();
//			this.rs1=prep.getGeneratedKeys();
//			Serializable ret = null;
//			if (rs1.next()) {
//				ret = (Serializable) rs1.getObject(1);
//				groupruleid=Integer.parseInt(ret.toString());
//			}
			//增加规则组和客户端平台的对应关系
			String[] selected_platform=null;
			if(!"".equals(platformids)){
				selected_platform=platformids.split(",");
			}
			if (selected_platform!= null) {
				this.sql = "insert into news_grouprule (groupruleid, platformid) values (?,?)";
				Query	querya=em.createNativeQuery(sql);
			
				for (int i = 0; i < selected_platform.length; i++) {
					int platformid = Integer.parseInt(selected_platform [i]) ;
//					prep.setInt(1, groupruleid);
//					prep.setInt(2, platformid);
//					prep.addBatch();
					querya.setParameter(1, groupruleid);
					querya.setParameter(2, platformid);
					querya.executeUpdate();
					}
//				prep.executeBatch();
			}
			
//			conn.commit(); 
		} catch (Exception e) {
			logger.info("addNewsGroup_Rule fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	/**
	 * Find all the NewsClientinfo Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsClientinfo>
	 */
	@Transactional(readOnly = true)
	public List<NewsClientinfo> findAllClientinfoEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsClientinfo clientinfo=(NewsClientinfo) hashMap.get("clientinfo");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String selectedSet=(String) hashMap.get("selectedSet");
		String groupids=(String) hashMap.get("groupids");
		Boolean hasAreaAll=(Boolean) hashMap.get("hasAreaAll");
		List<NewsClientinfo> queryList=null;
		try {
			StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsClientinfo n WHERE 1=1 ");
			if(clientinfo!=null){
				if(clientinfo.getArea()!=null){
					sqlS.append(" AND n.area in ("
							+ clientinfo.getArea() + ")");
				}
				if(clientinfo.getMpaltform()!=null&& clientinfo.getMpaltform().toString().trim().length() > 0){
					sqlS.append(" AND n.mpaltform like '%"
							+ clientinfo.getMpaltform().toString().trim() + "%'");
				}
				if(clientinfo.getMagent()!=null&& clientinfo.getMagent().toString().trim().length() > 0){
					sqlS.append(" AND n.magent like '%"
							+ clientinfo.getMagent().toString().trim() + "%'");
				}
				if(clientinfo.getMserialnum()!=null&& clientinfo.getMserialnum().toString().trim().length() > 0){
					sqlS.append(" AND n.mserialnum like '%"
							+ clientinfo.getMserialnum().toString().trim() + "%'");
				}
				if(clientinfo.getMencoding()!=null&& clientinfo.getMencoding().toString().trim().length() > 0){
					sqlS.append(" AND n.mencoding like '%"
							+ clientinfo.getMencoding().toString().trim() + "%'");
				}
				if(clientinfo.getClientid()!=null&& clientinfo.getClientid().toString().trim().length() > 0){
					sqlS.append(" AND n.clientid='"
							+ clientinfo.getClientid().toString().trim() + "'");
				}
			}
			Query query=null;
			if(selectedSet==null){
				if(groupids==null){
					//为root
					sqlS.append(" AND clientid not in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
					if(jpage!=null){
						if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
							sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
						}
					}
				}else{
					if(hasAreaAll){
						sqlS.append(" AND mencoding is not null and mencoding!='' and mencoding in (select newsSnAreaPK.sn from NewsSnArea)");
					}else{
						sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid in ("+groupids+"))");
					}
					sqlS.append(" AND clientid not in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
					if(jpage!=null){
						if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
							sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
						}
					}
				}
			}else{
				sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
				if(jpage!=null){
					if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
						sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
					}
				}
				
			}
			query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
			for(NewsClientinfo c:queryList){
				if(c.getArea()!=null&&!"".equals(c.getArea())){
					sqlS = new StringBuffer("SELECT t.areaname FROM TAreainfo t WHERE 1=1 AND areaid=:areaid");
					query = em.createQuery(sqlS.toString());
					query.setParameter("areaid", Integer.parseInt(c.getArea()));
					if(query.getResultList().size()>0){
						c.setArea(query.getResultList().get(0)+"");
					}else{
						c.setArea(null);
					}
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	//自定义组数据，全部加入、全部删除时，条件集合
	public List<Integer> findAllClientinfoEntityUserset(DataMap hashMap){
		NewsClientinfo clientinfo=(NewsClientinfo) hashMap.get("clientinfo");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String selectedSet=(String) hashMap.get("selectedSet");
		String groupids=(String) hashMap.get("groupids");
		Boolean hasAreaAll=(Boolean) hashMap.get("hasAreaAll");
		List<Integer> queryList=null;
		try {
			StringBuffer sqlS = new StringBuffer("SELECT n.clientid FROM NewsClientinfo n WHERE 1=1 ");
			if(clientinfo!=null){
				if(clientinfo.getArea()!=null){
					sqlS.append(" AND n.area in ("
							+ clientinfo.getArea() + ")");
				}
				if(clientinfo.getMpaltform()!=null&& clientinfo.getMpaltform().toString().trim().length() > 0){
					sqlS.append(" AND n.mpaltform like '%"
							+ clientinfo.getMpaltform().toString().trim() + "%'");
				}
				if(clientinfo.getMagent()!=null&& clientinfo.getMagent().toString().trim().length() > 0){
					sqlS.append(" AND n.magent like '%"
							+ clientinfo.getMagent().toString().trim() + "%'");
				}
				if(clientinfo.getMserialnum()!=null&& clientinfo.getMserialnum().toString().trim().length() > 0){
					sqlS.append(" AND n.mserialnum like '%"
							+ clientinfo.getMserialnum().toString().trim() + "%'");
				}
				if(clientinfo.getMencoding()!=null&& clientinfo.getMencoding().toString().trim().length() > 0){
					sqlS.append(" AND n.mencoding like '%"
							+ clientinfo.getMencoding().toString().trim() + "%'");
				}
				if(clientinfo.getClientid()!=null&& clientinfo.getClientid().toString().trim().length() > 0){
					sqlS.append(" AND n.clientid='"
							+ clientinfo.getClientid().toString().trim() + "'");
				}
			}
			Query query=null;
			if(selectedSet==null){
				if(groupids==null){
					//为root
					sqlS.append(" AND clientid not in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
				}else{
					if(hasAreaAll){
						sqlS.append(" AND mencoding is not null and mencoding!='' and mencoding in (select newsSnAreaPK.sn from NewsSnArea)");
					}else{
						sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid in ("+groupids+"))");
					}
					sqlS.append(" AND clientid not in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
				}
			}else{
				sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
			}
			query = em.createQuery(sqlS.toString());
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * 终端id 记录数
	 * @param hashMap
	 * @return
	 */
	public int findAllClientinfoEntityCount(DataMap hashMap){
		NewsClientinfo clientinfo=(NewsClientinfo) hashMap.get("clientinfo");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String selectedSet=(String) hashMap.get("selectedSet");
		Integer iCount = 0; 
		try {
			StringBuffer sqlS = new StringBuffer("SELECT count(n.clientid) FROM NewsClientinfo n WHERE 1=1 ");
			if(clientinfo!=null){
				if(clientinfo.getArea()!=null){
					sqlS.append(" AND n.area in ("
							+ clientinfo.getArea() + ")");
				}
				if(clientinfo.getMpaltform()!=null&& clientinfo.getMpaltform().toString().trim().length() > 0){
					sqlS.append(" AND n.mpaltform like '%"
							+ clientinfo.getMpaltform().toString().trim() + "%'");
				}
				if(clientinfo.getMagent()!=null&& clientinfo.getMagent().toString().trim().length() > 0){
					sqlS.append(" AND n.magent like '%"
							+ clientinfo.getMagent().toString().trim() + "%'");
				}
				if(clientinfo.getMserialnum()!=null&& clientinfo.getMserialnum().toString().trim().length() > 0){
					sqlS.append(" AND n.mserialnum like '%"
							+ clientinfo.getMserialnum().toString().trim() + "%'");
				}
				if(clientinfo.getMencoding()!=null&& clientinfo.getMencoding().toString().trim().length() > 0){
					sqlS.append(" AND n.mencoding like '%"
							+ clientinfo.getMencoding().toString().trim() + "%'");
				}
				if(clientinfo.getClientid()!=null&& clientinfo.getClientid().toString().trim().length() > 0){
					sqlS.append(" AND n.clientid='"
							+ clientinfo.getClientid().toString().trim() + "'");
				}
			}
			if(selectedSet==null){
				sqlS.append(" AND clientid not in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
			}else{
				sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
			}
			
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	
	//非root用户构建自定义组的抽取的终端id总记录数
	public int findAllClientinfoEntityCount2(DataMap hashMap){
		NewsClientinfo clientinfo=(NewsClientinfo) hashMap.get("clientinfo");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String selectedSet=(String) hashMap.get("selectedSet");
		String groupids=(String) hashMap.get("groupids");
		Boolean hasAreaAll=(Boolean) hashMap.get("hasAreaAll");
		Integer iCount = 0; 
		try {
			StringBuffer sqlS = new StringBuffer("SELECT count(n.clientid) FROM NewsClientinfo n WHERE 1=1 ");
			if(clientinfo!=null){
				if(clientinfo.getArea()!=null){
					sqlS.append(" AND n.area in ("
							+ clientinfo.getArea() + ")");
				}
				if(clientinfo.getMpaltform()!=null&& clientinfo.getMpaltform().toString().trim().length() > 0){
					sqlS.append(" AND n.mpaltform like '%"
							+ clientinfo.getMpaltform().toString().trim() + "%'");
				}
				if(clientinfo.getMagent()!=null&& clientinfo.getMagent().toString().trim().length() > 0){
					sqlS.append(" AND n.magent like '%"
							+ clientinfo.getMagent().toString().trim() + "%'");
				}
				if(clientinfo.getMserialnum()!=null&& clientinfo.getMserialnum().toString().trim().length() > 0){
					sqlS.append(" AND n.mserialnum like '%"
							+ clientinfo.getMserialnum().toString().trim() + "%'");
				}
				if(clientinfo.getMencoding()!=null&& clientinfo.getMencoding().toString().trim().length() > 0){
					sqlS.append(" AND n.mencoding like '%"
							+ clientinfo.getMencoding().toString().trim() + "%'");
				}
				if(clientinfo.getClientid()!=null&& clientinfo.getClientid().toString().trim().length() > 0){
					sqlS.append(" AND n.clientid='"
							+ clientinfo.getClientid().toString().trim() + "'");
				}
			}
			if(selectedSet==null){
				if(hasAreaAll){
					sqlS.append(" AND mencoding is not null and mencoding!='' and mencoding in (select newsSnAreaPK.sn from NewsSnArea)");
				}else{
					sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid in ("+groupids+"))");
				}
				sqlS.append(" AND clientid not in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
			}else{
				sqlS.append(" AND clientid in (SELECT clientid FROM NewsGroupuserset WHERE groupruleid='"+group.getGroupruleid()+"')");
			}
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * SN与区域对应信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllSnAreaEntityCount(DataMap hashMap){
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.newsSnAreaPK.sn) FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		if(selectedSet==null){
			//SN列表||全部加入
			sqlS.append(" AND n.newsSnAreaPK.sn not in (SELECT newsGroupSnPK.sn FROM NewsGroupSn WHERE groupid='"+group.getGroupruleid()+"')");
		}else{
			//已选SN列表||全部删除
			sqlS.append(" AND n.newsSnAreaPK.sn in (SELECT newsGroupSnPK.sn FROM NewsGroupSn WHERE groupid='"+group.getGroupruleid()+"')");
		}
		try {
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * Find all the NewsSnArea Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<SnAreaVo>
	 */
	public List<SnAreaVo> findAllSnAreaEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		
		if(selectedSet==null){
			//SN列表||全部加入
			sqlS.append(" AND n.newsSnAreaPK.sn not in (SELECT newsGroupSnPK.sn FROM NewsGroupSn WHERE groupid='"+group.getGroupruleid()+"')");
		}else{
			//已选SN列表||全部删除
			sqlS.append(" AND n.newsSnAreaPK.sn in (SELECT newsGroupSnPK.sn FROM NewsGroupSn WHERE groupid='"+group.getGroupruleid()+"')");
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<SnAreaVo> list=new ArrayList<SnAreaVo>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			List<NewsSnArea> queryList=new ArrayList<NewsSnArea>();
			queryList = query.getResultList();
			SnAreaVo snAreaVo = null;
			for(NewsSnArea snarea_temp:queryList){
				snAreaVo=new SnAreaVo();
				snAreaVo.setSn(snarea_temp.getNewsSnAreaPK().getSn());
				snAreaVo.setAreaid(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaid()+"");
				snAreaVo.setAreaname(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaname());
				list.add(snAreaVo);
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * SN与区域对应信息总记录数
	 * @param hashMap
	 * @return Integer
	 * add 20111014 for hubei t_tel_device增加字段，按用户类型行业地域搜索
	 */
	public Integer findAllSnAreaEntityCountNative(DataMap hashMap)
	{
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		Integer iCount = 0; 
		//new
		String userType = (String) hashMap.get("usertype");
		String profession=(String) hashMap.get("profession");
		String tarea=(String) hashMap.get("tarea");
		this.sql = "SELECT count(*) from news_sn_area nsa left join t_areainfo ta on nsa.areaname=ta.areaid ";
		if(userType !=null || profession!=null || tarea != null)
		{
			sql += ",t_tel_device td where nsa.sn=td.devicesn ";
			if(userType !=null)
				sql += " and td.user_type="+userType;
			if(profession !=null)
				sql += " and td.profession like '%"+profession+"%'";
			if(tarea !=null)
				sql += " and td.tarea like '%"+tarea+"%'";
		}
		else
			sql += " where 1=1 ";
		if(snarea!=null)
		{
			String sn = snarea.getNewsSnAreaPK().getSn().trim();
			if(sn!=null && sn.length() > 0)
			{
				sql += " and nsa.sn like '%"+sn+"%'";
			}
		}
		if(areaids!=null)
		{
			sql += " and ta.areaid in ("+ areaids + ")";
		}
		if(selectedSet==null){
			//SN列表||全部加入
			sql += " and nsa.sn not in (select sn from news_group_sn where groupid="+group.getGroupruleid()+")";
		}else{
			//已选SN列表||全部删除
			sql += " and nsa.sn in (select sn from news_group_sn where groupid="+group.getGroupruleid()+")";
		}
		try {
			Query query = em.createNativeQuery(sql);
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	public List<SnAreaVo> findAllSnAreaEntityNative(DataMap hashMap, Integer firstrow,Integer maxrow)
	{
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		//new
		List<SnAreaVo> result=new ArrayList<SnAreaVo>();
		String userType = (String) hashMap.get("usertype");
		String profession=(String) hashMap.get("profession");
		String tarea=(String) hashMap.get("tarea");
		this.sql = "SELECT nsa.sn,ta.areaid,ta.areaname from news_sn_area nsa left join t_areainfo ta on nsa.areaname=ta.areaid ";
		if(userType !=null || profession!=null || tarea != null)
		{
			sql += ",t_tel_device td where nsa.sn=td.devicesn ";
			if(userType !=null)
				sql += " and td.user_type="+userType;
			if(profession !=null)
				sql += " and td.profession like '%"+profession+"%'";
			if(tarea !=null)
				sql += " and td.tarea like '%"+tarea+"%'";
		}
		else
			sql += " where 1=1 ";
		if(snarea!=null)
		{
			String sn = snarea.getNewsSnAreaPK().getSn().trim();
			if(sn!=null && sn.length() > 0)
			{
				sql += " and nsa.sn like '%"+sn+"%'";
			}
		}
		if(areaids!=null)
		{
			sql += " and ta.areaid in ("+ areaids + ")";
		}
		if(selectedSet==null)
		{
			//SN列表||全部加入
			sql += " and nsa.sn not in (select sn from news_group_sn where groupid="+group.getGroupruleid()+")";
		}
		else
		{
			//已选SN列表||全部删除
			sql += " and nsa.sn in (select sn from news_group_sn where groupid="+group.getGroupruleid()+")";
		}
		if(jpage!=null)
		{
			String col = jpage.getSidx();
			if(col!=null&&!"".equals(col))
			{
				if(col.contains(".sn"))
					col = "sn";
				if(col.contains(".areaname"))
					col = "areaname";
				sql += " order by "+col+" "+jpage.getSord();
			}
		}
		try {
			Query query = em.createNativeQuery(sql);
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			List<Object[]> tmp = query.getResultList();
			if(tmp != null && tmp.size()>0)
			{
				for (Object[] objects : tmp) 
	    		{
	    			if (objects != null && objects.length > 0) 
	    			{
	    				int j = 0;
	    				SnAreaVo one = new SnAreaVo();
	    				String sn = (String)objects[j++];
	    				one.setSn(sn);
	    				String areaid = objects[j++]+"";
	    				if(areaid.contains("."))
	    					one.setAreaid(areaid.substring(0,areaid.indexOf(".")));
	    				else
	    					one.setAreaid(areaid);
	    				String areaname = (String)objects[j++];
	    				one.setAreaname(areaname);
						result.add(one);
	    			}
	    		}
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		
		return result;
	}
	
	private Long converObjtoLong(Object o) 
	{
		if (o == null)
			return 0L;
		else if (o instanceof Long)
			return (Long) o;
		else if (o instanceof Integer)
			return ((Integer) o).longValue();
		else
			return Long.parseLong(o.toString());
	
	}
	
	/***
	 * 新增自定义组信息
	 * addNewsGroup_Rule 
	 * @param DataMap
	 * @return int   -1:失败	-2:记录已存在
	 */
	public int addNewsGroup_Userset(DataMap hashMap) {
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String user_id=(String) hashMap.get("userId");;
		int groupruleid = -1;
		try {
//			this.init();
//			//增加自定义组信息
//			this.sql="insert into news_group (grouprulename,mlocale,note,grouptype,userid,createtime) values (?,?,?,2,'"+user_id+"',now())";
//			this.prep=this.conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
//			prep.setString(1, group.getGrouprulename());
//			prep.setString(2, group.getMlocale());
//			prep.setString(3, group.getNote());
//			prep.executeUpdate();
//			this.rs1=prep.getGeneratedKeys();
//			Serializable ret = null;
//			if (rs1.next()) {
//				ret = (Serializable) rs1.getObject(1);
//				groupruleid=Integer.parseInt(ret.toString());
//			}
			group.setUserid(user_id);
			group.setGrouptype(2);
			group.setCreatetime(new Date());
			this.em.persist(group);
			groupruleid=group.getGroupruleid();
			
		} catch (Exception e) {
			logger.info("addNewsGroup_Userset fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return groupruleid;
	}

	/**
	 * 增加自定义组和终端id的关系
	 * @param dataMap
	 * @return Boolean
	 */
	public Boolean addUserset(DataMap hashMap) {
		long aa1 = System.currentTimeMillis();
		NewsGroup group=(NewsGroup) hashMap.get("group");
		List<Integer> clientidList= (List<Integer>)hashMap.get("clientidList");
		String strIdList = (String) hashMap.get("strIdList");
		Boolean resultb=true;
		String [] strIdListarys=null;
		try {
		
			if(clientidList!=null){
				//全部加入
				strIdListarys=new String[clientidList.size()];
				for(int i=0;i<clientidList.size();i++){
					strIdListarys[i]=clientidList.get(i)+"";
				}
			}else if(strIdList!=null&&!"".equals(strIdList)){
				//加入,一个或多个
				strIdListarys= strIdList.split(",");
			}else{
				return true;
			}
			
			int iii=0;
			sql="insert into news_groupuserset (groupruleid, clientid) values ";
			String tempsql="";
			for (int i=0;i<strIdListarys.length;i++) {
				tempsql+="("+ group.getGroupruleid()+","+strIdListarys[i]+"),";
				iii++;
				if(iii>100)
    			{	
					tempsql=tempsql.substring(0,tempsql.length()-1);
					this.em.createNativeQuery(sql+tempsql).executeUpdate();
					//this.stmt.executeUpdate(sql+tempsql);
					tempsql="";
    				iii=0;
    			}
				
			}
			if(iii>0)
			{	
				tempsql=tempsql.substring(0,tempsql.length()-1);
				this.em.createNativeQuery(sql+tempsql).executeUpdate();
			}
			/*for (String clientid : strIdListarys) {
				prep.setInt(1, group.getGroupruleid());
				prep.setInt(2, Integer.parseInt(clientid));
				prep.addBatch();
				iii++;
				if(iii>100)
    			{	
    				prep.executeBatch();
    				iii=0;
    			}
			}
			if(iii>0)
			{	
				prep.executeBatch();
				iii=0;
			}*/
			long aa2 = System.currentTimeMillis();
			logger.info("----insert into news_groupuserset (groupruleid, clientid) values (?, ?):"+(aa2-aa1));
			Set<String> snSet=new HashSet<String>();
			String sn="";
			
			//prep2=conn.prepareStatement(sql);
			for (String clientid : strIdListarys) {
				this.sql="select mencoding from news_clientinfo where clientid="+clientid;
				snSet.add(this.getOneString());
			}
			snSet.remove("");
			snSet.remove(null);
			long aa3 = System.currentTimeMillis();		
			logger.info("----select mencoding from news_clientinfo where clientid=?:"+(aa3-aa2));
			logger.info("-----snSet:"+snSet.size());
			//如果clientid对应sn存在，则加入到news_group_sn
			
		
			List<String> snList=new ArrayList<String>();
			for (String snn:snSet) {
	          
				this.sql="select sn from news_group_sn where groupid="+group.getGroupruleid()+" and sn='"+snn+"'";
				snList.add(this.getOneString());
				
	        }
			long aa4 = System.currentTimeMillis();		
			logger.info("-----select sn from news_group_sn where groupid=? and sn=?:"+(aa4-aa3));
			
			iii=0;
			tempsql="";
			sql="insert into news_group_sn (groupid, sn) values";
			for (int i=0;i<snList.size();i++) {
				tempsql+="("+ group.getGroupruleid()+",'"+snList.get(i)+"'),";
				iii++;
				if(iii>100)
    			{	
					tempsql=tempsql.substring(0,tempsql.length()-1);
					this.em.createNativeQuery(sql+tempsql).executeUpdate();
					tempsql="";
    				iii=0;
    			}
				
			}
			if(iii>0)
			{	
				tempsql=tempsql.substring(0,tempsql.length()-1);
				this.em.createNativeQuery(sql+tempsql).executeUpdate();
			}
			/*for(String sn2:snList){
				prep1.setInt(1, group.getGroupruleid());
				prep1.setString(2, sn2);
				prep1.addBatch();
				iii++;
				if(iii>100)
    			{	
					prep1.executeBatch();
    				iii=0;
    			}
			}
			if(iii>0)
			{	
				prep1.executeBatch();
			}*/
			long aa5 = System.currentTimeMillis();
			logger.info("----insert into news_group_sn (groupid, sn) values (?, ?):"+(aa5-aa4));
			
		} catch (Exception e) {
			logger.info("addUserset fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	/**
	 * 删除自定义组和终端id的关系
	 * @param dataMap
	 * @return Boolean
	 */
	public Boolean delUserset(DataMap hashMap) {
		NewsGroup group=(NewsGroup) hashMap.get("group");
		List<Integer> clientidList= (List<Integer>)hashMap.get("clientidList");
		String strIdList = (String) hashMap.get("strIdList");
		Boolean resultb=true;
		String[] strIdListarys=null;
		try {
			
			
		
			if(clientidList!=null){
				//全部删除
				strIdListarys=new String[clientidList.size()];
				for(int i=0;i<clientidList.size();i++){
					strIdListarys[i]=clientidList.get(i)+"";
				}
			}else if(!"".equals(strIdList)){
				strIdListarys=strIdList.split(",");
				//删除,一个或多个
			}else{
				return true;
			}
			long aa1 = System.currentTimeMillis();
			int iii=0;
			for (String clientid : strIdListarys) {
				this.sql="delete from news_groupuserset where clientid="+clientid+" and groupruleid="+group.getGroupruleid();
				this.em.createNativeQuery(sql).executeUpdate();
			}
			long aa2 = System.currentTimeMillis();
			logger.info("----delete from news_groupuserset where clientid=? and groupruleid=?:"+(aa2-aa1));
			
		//	prep2=conn.prepareStatement(sql);
			Set<String> snSet=new HashSet<String>();
			for (String clientid : strIdListarys) {
				sql="select mencoding from news_clientinfo where clientid="+clientid;
//				prep2.setInt(1, Integer.parseInt(clientid));
//				rs1=prep2.executeQuery();
//				if(rs1.next()){
					//从news_group_sn中删除
					snSet.add(this.getOneString());
//				}
			}
			snSet.remove("");
			snSet.remove(null);
			
		
			for (String sn:snSet) {
	          
				this.sql="delete from news_group_sn where groupid="+group.getGroupruleid()+" and sn='"+sn+"'";
				this.em.createNativeQuery(sql).executeUpdate();
			}
		
			
			/*this.sql="select clientid from news_groupuserset where groupruleid=? and clientid in (select clientid from news_clientinfo where mencoding=(select mencoding  from news_clientinfo where clientid=? and mencoding!='') and clientid!=?)";
			prep2=conn.prepareStatement(sql);
			for (String clientid : strIdListarys) {
				prep2.setInt(1, group.getGroupruleid());
				prep2.setInt(2, Integer.parseInt(clientid));
				prep2.setInt(3, Integer.parseInt(clientid));
				rs1=prep2.executeQuery();
				if(!rs1.next()){
					this.sql="select sn from news_group_sn where groupid=? and sn=(select mencoding from news_clientinfo where clientid=?)";
					prep2=conn.prepareStatement(sql);
					prep2.setInt(1, group.getGroupruleid());
					prep2.setInt(2, Integer.parseInt(clientid));
					rs1=prep2.executeQuery();
					if(rs1.next()){
						//如果clientid对应sn存在这个组中，则从news_group_sn中删除
						prep1.setInt(1, group.getGroupruleid());
						prep1.setString(2, rs1.getString(1));
						prep1.addBatch();
					}
				}
			}*/
			
			long aa3 = System.currentTimeMillis();
			logger.info("----select mencoding from news_clientinfo where clientid=?____delete from news_group_sn where groupid=? and sn=?:"+(aa3-aa2));
			
		} catch (Exception e) {
			logger.info("delUserset fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	/**
	 * 增加自定义组和SN的关系
	 * @param dataMap
	 * @return Boolean
	 */
	public Boolean addSnarea(DataMap hashMap) {
		NewsGroup group=(NewsGroup) hashMap.get("group");
		List<SnAreaVo> salist= (List<SnAreaVo>)hashMap.get("salist");
		String strIdList = (String) hashMap.get("strIdList");
		Boolean resultb=true;
		String [] strIdListarys=null;
		try {
			//this.init();
			
			
			Set<Integer> clientidSet=new HashSet<Integer>();
			Set<String> snSet=new HashSet<String>();
			if(salist!=null){
				//全部加入
				strIdListarys=new String[salist.size()];
				for(int i=0;i<salist.size();i++){
					strIdListarys[i]=salist.get(i).getSn()+"";
				}
			}else if(strIdList!=null&&!"".equals(strIdList)){
				//加入,一个或多个
				strIdListarys= strIdList.split(",");
			}else{
				return true;
			}
			for (String sn : strIdListarys) {
				
				this.sql="select clientid from news_clientinfo a, where mencoding='"+sn+"'";
//				prep2=conn.prepareStatement(sql);
//				prep2.setString(1, sn);
//				rs1=prep2.executeQuery();
//				while(rs1.next()){
//					//如果sn存在对应clientid，则加入到news_groupuserset中
//					this.sql="select clientid from news_groupuserset where groupruleid=? and clientid=?";
//					prep2=conn.prepareStatement(sql);
//					prep2.setInt(1, group.getGroupruleid());
//					prep2.setInt(2, rs1.getInt(1));
//					rs2=prep2.executeQuery();
//					if(!rs2.next()){
//						clientidSet.add(rs1.getInt(1));
//					}
//				}
//				this.sql="select sn from  news_group_sn where sn=? and groupid=?";
//				prep2=conn.prepareStatement(sql);
//				prep2.setString(1, sn);
//				prep2.setInt(2, group.getGroupruleid());
//				rs2=prep2.executeQuery();
//				if(!rs2.next()){
//					snSet.add(sn);
//				}
			}
			this.sql="insert into news_groupuserset (groupruleid, clientid) values (?, ?)";
		//	prep1=conn.prepareStatement(sql);
			
		//	NewsGroupuserset
			for (Iterator<Integer> it = clientidSet.iterator(); it.hasNext();) {
				Integer clientid = it.next();
//	            prep1.setInt(1, group.getGroupruleid());
//				prep1.setInt(2, clientid);
//				prep1.addBatch();
				NewsGroupuserset n=new NewsGroupuserset();
				n.setGroupruleid(group.getGroupruleid());
				n.setClientid(clientid);
				this.em.persist(n);
	        }
			
			this.sql="insert into news_group_sn (sn, groupid) values (?, ?)";
			//prep=conn.prepareStatement(sql);
			for (Iterator<String> it = snSet.iterator(); it.hasNext();) {
	            String sn = it.next();
//	            prep.setString(1, sn);
//	            prep.setInt(2, group.getGroupruleid());
//				prep.addBatch();
				NewsGroupSn s =new NewsGroupSn();
				NewsGroupSnPK spk=new NewsGroupSnPK(sn,group.getGroupruleid());
				s.setNewsGroupSnPK(spk);
				this.em.persist(s);
	        }
//			prep1.executeBatch();
//			prep.executeBatch();
//			conn.commit(); 
		} catch (Exception e) {
			logger.info("addSnarea fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	/**
	 * 删除自定义组和SN的关系
	 * @param dataMap
	 * @return Boolean
	 */
	public Boolean delSnarea(DataMap hashMap) {
		NewsGroup group=(NewsGroup) hashMap.get("group");
		List<SnAreaVo> salist= (List<SnAreaVo>)hashMap.get("salist");
		String strIdList = (String) hashMap.get("strIdList");
		Boolean resultb=true;
//		try {
//			this.init();
//			this.sql="delete from news_group_sn where sn=? and groupid=?";
//			prep=conn.prepareStatement(sql);
//			this.sql="delete from news_groupuserset where groupruleid=? and clientid=?";
//			prep1=conn.prepareStatement(sql);
//			if(salist!=null){
//				//全部删除
//				for(SnAreaVo snArea:salist){
//					this.sql="select clientid from news_groupuserset where groupruleid=? and clientid in (select clientid from news_clientinfo where mencoding=?)";
//					prep2=conn.prepareStatement(sql);
//					prep2.setInt(1, group.getGroupruleid());
//					prep2.setString(2, snArea.getSn());
//					rs1=prep2.executeQuery();
//					while(rs1.next()){
//						//如果sn对应clientid存在这个组中，则从news_groupuserset中删除
//						prep1.setInt(1, group.getGroupruleid());
//						prep1.setInt(2, rs1.getInt(1));
//						prep1.addBatch();
//					}
//					prep.setString(1, snArea.getSn());
//					prep.setInt(2, group.getGroupruleid());
//					prep.addBatch();
//				}
//			}else if(!"".equals(strIdList)){
//				String[] strIdListArys=strIdList.split(",");
//				//删除,一个或多个
//				for (String sn : strIdListArys) {
//					this.sql="select clientid from news_groupuserset where groupruleid=? and clientid in (select clientid from news_clientinfo where mencoding=?)";
//					prep2=conn.prepareStatement(sql);
//					prep2.setInt(1, group.getGroupruleid());
//					prep2.setString(2, sn);
//					rs1=prep2.executeQuery();
//					while(rs1.next()){
//						//如果sn对应clientid存在这个组中，则从news_groupuserset中删除
//						prep1.setInt(1, group.getGroupruleid());
//						prep1.setInt(2, rs1.getInt(1));
//						prep1.addBatch();
//					}
//					prep.setString(1, sn);
//					prep.setInt(2, group.getGroupruleid());
//					prep.addBatch();
//				}
//			}else{
//				return true;
//			}
//			prep.executeBatch();
//			prep1.executeBatch();
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.info("delUserset fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}
	
	/**
	 * 删除用户组信息
	 * 1.//删除自定义组关联
	 * 2.//删除规则组关联
	 * 3.//删除消息关联
	 * 4.//删除组信息
	 * @param groupruleid
	 * @return	Boolean
	 */
	public Boolean deleteNewsGroupById(Integer groupruleid) {
		Boolean resultb=true;
		try {
//			this.init();
			//删除自定义组关联
			this.sql="delete from news_groupuserset where groupruleid="+groupruleid;
			this.em.createQuery(sql).executeUpdate();
			
			//删除规则组关联
			this.sql="delete from news_grouprule where groupruleid="+groupruleid;
			this.em.createQuery(sql).executeUpdate();
			
			//删除消息关联
			this.sql="delete from news_systempopumsggroup where groupruleid="+groupruleid;
			this.em.createQuery(sql).executeUpdate();
			
			//删除组信息
			this.sql="delete from news_group where groupruleid="+groupruleid;
			this.em.createQuery(sql).executeUpdate();
			
//			conn.commit(); 
		} catch (Exception e) {
			logger.info("deleteNewsGroupById fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	/***
	 * 更新组信息
	 * updateNewsGroup 
	 * @param DataMap
	 * @return Boolean
	 */
	public Boolean updateNewsGroup(DataMap hashMap) {
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String platformids= (String)hashMap.get("platformids");
		Boolean resultb=true;
//		try {
////			this.init();
//			/*this.sql="SELECT groupruleid FROM news_group WHERE grouprulename=? AND groupruleid!=?";
//			this.prep=this.conn.prepareStatement(sql);	
//			this.prep.setString(1, group.getGrouprulename());
//			this.prep.setInt(2, group.getGroupruleid());
//			this.rs1=prep.executeQuery();
//			if(this.rs1.next()){
//				return null;
//			}*/
//			if(platformids!=null){
//				//检查规则组是否存在
//				this.sql="SELECT groupruleid FROM news_group WHERE grouprulename=? and mlocale=? and marea=? and note=? and grouptype=? and groupruleid!=?";
//				this.prep=this.conn.prepareStatement(sql);	
//				this.prep.setString(1, group.getGrouprulename());
//				this.prep.setString(2, group.getMlocale());
//				this.prep.setString(3, group.getMarea().getAreaid()+"");
//				this.prep.setString(4, group.getNote());
//				this.prep.setInt(5, 1);
//				this.prep.setInt(6, group.getGroupruleid());
//				this.rs1=prep.executeQuery();
//				while(this.rs1.next()){
//					String charIdList = "";
//					for (String str : platformids.split(",")) {
//						charIdList = charIdList + "'" + str + "',";
//					}
//					if (!charIdList.equals("")) {
//						charIdList = charIdList.substring(0, charIdList.length() - 1);
//					} 
//					this.sql="SELECT groupruleid FROM news_grouprule WHERE groupruleid=? and platformid in ("+charIdList+")";
//					this.prep=this.conn.prepareStatement(sql);	
//					this.prep.setInt(1, rs1.getInt(1));
//					this.rs2=prep.executeQuery();
//					if(this.rs2.next()){
//						return null;
//					}
//				}
//
//			}
//						
//			//更新规则组信息
//			this.sql="update news_group set grouprulename=?,mlocale=?,marea=?,note=? where groupruleid=?";
//			this.prep=this.conn.prepareStatement(sql);
//			prep.setString(1, group.getGrouprulename());
//			prep.setString(2, group.getMlocale());
//			if(group.getMarea()!=null){
//				prep.setString(3, group.getMarea().getAreaid()+"");
//			}else{
//				prep.setString(3, null);
//			}
//			prep.setString(4, group.getNote());
//			prep.setInt(5, group.getGroupruleid());
//			prep.executeUpdate();
//			
//			if(platformids!=null){
//				//更新规则组和客户端平台的对应关系
//				//1.删除规则组和客户端平台的对应关系
//				this.sql="delete from news_grouprule where groupruleid=?";
//				this.prep=this.conn.prepareStatement(sql);
//				prep.setInt(1, group.getGroupruleid());
//				prep.executeUpdate();
//				//2.增加规则组和客户端平台的对应关系
//				String[] selected_platform=null;
//				if(!"".equals(platformids)){
//					selected_platform=platformids.split(",");
//				}
//				if (selected_platform!= null) {
//					this.sql = "insert into news_grouprule (groupruleid, platformid) values (?,?)";
//					this.prep=this.conn.prepareStatement(sql);
//					for (int i = 0; i < selected_platform.length; i++) {
//						int platformid = Integer.parseInt(selected_platform [i]) ;
//						prep.setInt(1, group.getGroupruleid());
//						prep.setInt(2, platformid);
//						prep.addBatch();
//						}
//					prep.executeBatch();
//				}
//			}
//			
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.info("updateNewsGroup_Userset fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}

	public String findAllGroupids(String userId,Integer grouptype) {
		String groupids = "";
		try {
	        String spcodeS=findAllSpcodes(userId);
	     
			sql="select distinct tsr.rulegroup from t_spcode_rulegroup tsr join news_group ng on tsr.rulegroup=ng.groupruleid where spcode in ("+spcodeS+")";
			if(grouptype!=null){
				sql+=" and ng.grouptype="+grouptype+"";
			}
			
			String groupid=null;
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				groupid=this.getString(o[0]);
				groupids+="'"+groupid+"',";
			}
			if(!"".equals(groupids)){
				groupids=groupids.substring(0, groupids.length()-1);
        	}
		 }catch (Exception e) {
				logger.error("findAllGroupids fail !!!" + e.getMessage());
				e.printStackTrace();
				
		}
		if("".equals(groupids)){
			groupids="''";
		}
		return groupids;
	}
	
	public String findAllGroupids_CP(String userId,Integer grouptype) {
		String groupids = "";
		String spcodeS = "''";
		try {
//	        this.init();
				sql="select te.sp_code from t_employee te join t_user tu on te.employee_id=tu.employee_id where user_id="+userId;
//			prep=conn.prepareStatement(sql);
//			prep.setString(1, userId);
//			rs1=prep.executeQuery();
			String spcode = null;
//			if(rs1.next()){
//				spcode=rs1.getString(1);
//			}
			spcode=this.getOneString();
//			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
//			for(Object[]o:tempp)
//			{
			if(spcode!=null){
				spcodeS="'"+spcode+"'";
			}
			sql="select distinct tsr.rulegroup from t_spcode_rulegroup tsr join news_group ng on tsr.rulegroup=ng.groupruleid where spcode in ("+spcodeS+")";
			if(grouptype!=null){
				sql+=" and ng.grouptype="+grouptype+"";
			}
//			rs1=stmt.executeQuery(sql);
			String groupid=null;
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				groupid=this.getString(o[0]);
				groupids+="'"+groupid+"',";
			}
			if(!"".equals(groupids)){
				groupids=groupids.substring(0, groupids.length()-1);
        	}
		 }catch (Exception e) {
				logger.error("findAllGroupids fail !!!" + e.getMessage());
				e.printStackTrace();
		}
		if("".equals(groupids)) groupids="''";
		return groupids;
	}
	

	/**
	 * 标识是否存在规则组的区域为全国的记录
	 * @param groupids
	 * @return
	 */
	public boolean findGroup_areaAll(String groupids) {
		boolean resultb=false;
		try {
			sql="select count(groupruleid) from news_group where marea=0 and groupruleid in ("+groupids+")";
			resultb =this.getOneInt()>0;
		 }catch (Exception e) {
				logger.error("findGroup_areaAll fail !!!" + e.getMessage());
				e.printStackTrace();
		}
		return resultb;
	}
}
